Oracle的CLOB大数据字段类型 |
您所在的位置：网站首页 › oracle超过4000的字符 clob › Oracle的CLOB大数据字段类型 |
Oracle的CLOB大数据字段类型
|
一、Oracle中的varchar2类型
我们在Oracle数据库存储的字符数据一般是用VARCHAR2。VARCHAR2既分PL/SQL Data Types中的变量类型，也分Oracle Database中的字段类型，不同场景的最大长度不同。 在Oracle Database中，VARCHAR2 字段类型，最大值为4000；PL/SQL中 VARCHAR2 变量类型，最大字节长度为32767。 当 VARCHAR2 容纳不下我们需要存储的信息时，就出来的Oracle的大数据类型LOB( Large Object，大型对象)。 二、Oarcle中的LOB类型 在Oracle中，LOB（Large Object，大型对象）类型的字段现在用得越来越多了。因为这种类型的字段，容量大（最多能容纳4GB的数据），且一个表中可以有多个这种类型的字段，很灵活，适用于数据量非常大的业务领域（如图象、档案等）。 LOB类型分为BLOB和CLOB两种：BLOB即二进制大型对象（Binary Large Object），适用于存贮非文本的字节流数据（如程序、图象、影音等）。 而CLOB，即字符型大型对象（Character Large Object），则与字符集相关，适于存贮文本型的数据（如历史档案、大部头著作等）。 三、DB中使用CLOB类型字段 （一）、创建表（使用sql或者直接在PL/SQL客户端创建），字段类型CLOB -- Create table create table TEMP ( name VARCHAR2(200), age NUMBER, temp_clob CLOB ) tablespace INSIGHTDATA3_TS pctfree 10 initrans 1 maxtrans 255 storage ( initial 160K next 1M minextents 1 maxextents unlimited ); （二）、增删改查 先看一下使用普通方式操作CLOB类型： SELECT t.name, t.temp_clob FROM temp t; --普通查询 INSERT INTO temp t VALUES ('Grand.Jon', 22, '加入博客园的第一天'); 查询因为不是varchar2类型，所以普通查询看不到CLOB类型的字段内容，结果如下
而普通插入操作也会因为Oracle的隐式转换，默认把字符串转换成varchar2类型，一旦字符串内容超过varchar2的最大限度就会报会报ora-01704（字符串太长）错误。 正确操作 --使用PL/SQL语法，采取绑定变量的方式解决，而不是直接拼接SQL DECLARE V_LANG CLOB := '待插入的海量字符串'; V_UPDATE CLOB := '更新的海量字符串'; BEGIN INSERT INTO temp t VALUES ('Grand.Jon', 22, V_LANG); --增加 UPDATE temp t SET t.temp_clob = V_UPDATE WHERE rownum = 1; --修改 SELECT t.NAME, dbms_lob.substr(t.temp_clob) FROM TEMP t; --查询 将CLOB转成字符类型 DELETE temp t WHERE rownum = 1; --按列删除 COMMIT; END; /对CLOB的操作我们在存储过程中基本上使用 dbms_lob 中 substr , append , write 等方法。 dbms_lob 方法总结 1 dbms_lob.createtemporary(V_SQL,true); --创建一个临时clob，用来存储拼接的sql 2 dbms_lob.write(v_SQL,'写入信息'); --写入操作 3 dbms_lob.append(v_SQL,','); --拼接clob 4 dbms_lob.substr(v_SQL); --截取clob，不传参数就是全部读取 5 dbms_lob.freetemporary(v_SQL); --释放clob查询结果如下：
四、在存储过程中使用CLOB类型实例 需求：以开发的存储过程为例，需要循环遍历时间范围拼接sql，将时间日期按列反转（pivot），如果时间太长（1年以上）sql语句（varchar2）就会超出范围报错，这时候就需要使用CLOB来存储拼接的sql。 PROCEDURE P_AND_CPT_RATIOOTH_APP_BAK2_N( V_APPIDS IN VARCHAR2, V_TYPE IN VARCHAR2, V_CHANNEL IN VARCHAR2, V_TABLE IN VARCHAR2, V_START IN VARCHAR2, V_END IN VARCHAR2, RESULT OUT mycursor ) IS V_SQL CLOB; V_SQLWHERE VARCHAR2(32767) default ''; V_SQLWHERE_CHANNEL VARCHAR2(32767) default ''; V_SQL_DATES CLOB; V_Sdate DATE; V_Edate DATE; V_TABLE_DATE VARCHAR2(50); V_TABLE_TYPE VARCHAR2(50); V_START_DATE VARCHAR2(50); V_END_DATE VARCHAR2(50); V_DAY VARCHAR2(50); BEGIN select column_name into V_TABLE_DATE from user_tab_columns where table_name=''||V_TABLE||'' and column_id=1; select column_name into V_TABLE_TYPE from user_tab_columns where table_name=''||V_TABLE||'' and column_id=5; dbms_lob.createtemporary(V_SQL,true);--创建一个临时lob dbms_lob.createtemporary(V_SQL_DATES,true);--创建一个临时lob IF V_APPIDS is NOT NULL THEN V_SQLWHERE := 'AND t.appid in ('||V_APPIDS||')'; END IF; IF V_CHANNEL IS NOT NULL THEN V_SQLWHERE_CHANNEL := 'AND t.channel = '''||V_CHANNEL||''''; END IF; IF V_TABLE_DATE = 'MON' THEN V_START_DATE := SUBSTR(V_START,0,6); V_END_DATE := SUBSTR(V_END,0,6); v_sdate := to_date(V_START_DATE, 'yyyymm'); v_edate := to_date(V_END_DATE, 'yyyymm'); WHILE (v_sdate 7 THEN dbms_lob.append(v_SQL_DATES,',');--把临时字符串付给v_str END IF; END IF; v_sdate := v_sdate+1; END LOOP; END IF; END IF; dbms_lob.append(v_sql,'SELECT * FROM( SELECT * FROM '||V_TABLE||' t WHERE t.'||V_TABLE_TYPE||' = '''||V_TYPE||''' AND t.'||V_TABLE_DATE||' >= '''||V_START_DATE||''' AND t.'||V_TABLE_DATE||' |
【本文地址】
今日新闻 |
推荐新闻 |